import pandas as pd

# ======================
# 任务 1) 读取 Excel 文件，构造带列标签的 DataFrame
# ======================

# 文件路径
file_path = r'D:\桌面\Python实验\scores.xlsx'

# 1) 使用 pandas 读取 Excel 文件（加入 engine="openpyxl" 以确保兼容 .xlsx）
df_raw = pd.read_excel(file_path, engine="openpyxl")

# 打印原始数据结构，便于调试（可注释掉）
print("原始数据（前几行）：")
print(df_raw.head(10))  # 假设每年两行：文科 & 理科，每行含一本/二本
print("\n")


# ✅ 请根据你 Excel 文件中的实际列名修改以下四个变量：
YEAR_COL = '年份'  # 请替换为你的 Excel 中表示年份的列名
CATEGORY_COL = '类别'  # 请替换为你的 Excel 中表示“文科/理科”的列名，比如 '科类'
LEVEL_COL = '层次'  # 请替换为你的 Excel 中表示“一本/二本”的列名，比如 '批次'
SCORE_COL = '分数线'  # 请替换为你的 Excel 中表示分数的列名，比如 '分数'

# 确保这些列名和你的 Excel 文件一致，否则会报错 KeyError

# 只保留需要的列（如果有多余列，可以只选这四列）
df_raw = df_raw[[YEAR_COL, CATEGORY_COL, LEVEL_COL, SCORE_COL]]

# 查看处理后的原始列
print(f"使用的列：{YEAR_COL}, {CATEGORY_COL}, {LEVEL_COL}, {SCORE_COL}")
print(df_raw.head(10))
print("\n")

# ======================
# 2) 数据整理：将“每年两行（文科/理科），每行含一本/二本” -> 整理为“每年一行，包含四个分数”
# ======================

# 我们要构造一个新的 DataFrame，每一行代表一个年份，列包括：
# '年份', '一本文科', '一本理科', '二本文科', '二本理科'

# 初始化一个空列表，用于存储整理后的每一行数据
data_rows = []

# 获取所有唯一的年份
unique_years = df_raw[YEAR_COL].unique()

# 定义类别和层次
categories = ['文科', '理科']
levels = ['一本', '二本']

# 遍历每一个年份
for year in sorted(unique_years):  # 按年份排序
    row_dict = {'年份': year}

    # 遍历每个类别（文科/理科）
    for category in categories:
        # 遍历每个层次（一本/二本）
        for level in levels:
            # 构造当前组合：如 一本文科、一本理科...
            key = f"{level}{category}"
            row_dict[key] = None  # 先设为 None，后面填充

            # 在原始数据中查找：年份=year，类别=category，层次=level 的分数线
            mask = (
                    (df_raw[YEAR_COL] == year) &
                    (df_raw[CATEGORY_COL] == category) &
                    (df_raw[LEVEL_COL] == level)
            )
            matched = df_raw.loc[mask, SCORE_COL]

            if not matched.empty:
                # 只取第一个匹配到的分数（假设每个组合唯一）
                row_dict[key] = matched.iloc[0]
            # 如果没有匹配到，可能数据缺失，此处保持为 None

    # 将这一行的字典添加到结果列表中
    data_rows.append(row_dict)

# 构造最终的 DataFrame
df_obj = pd.DataFrame(data_rows)

# 打印整理后的数据（前几行）
print("1) 整理后的 DataFrame（每一年一行，包含四个分数线）：")
print(df_obj.head())
print("\n")

# ======================
# 接下来是你原来的一系列任务代码，可以直接沿用！！！
# ======================

# 任务 2) 按一本文科降序排序
sorted_obj = df_obj.sort_values(by='一本文科', ascending=False)
print("2) 按【一本文科】降序排序后的 DataFrame（前几行）：")
print(sorted_obj.head())
print("\n")

# 任务 3) 一本文科最大值
max_val = sorted_obj['一本文科'].max()
print("3) sorted_obj 中【一本文科】的最大值：", max_val)
print("\n")

# 任务 4) 一本文科最小值
min_val = sorted_obj['一本文科'].min()
print("4) sorted_obj 中【一本文科】的最小值：", min_val)
print("\n")

# 任务 5) 一本文科最大值与最小值之差
diff_1wen = sorted_obj['一本文科'].max() - sorted_obj['一本文科'].min()
print("5) 一本文科分数线 最大值与最小值之差：", diff_1wen)
print("\n")

# 任务 6) 一本理科最大值与最小值之差
diff_1li = sorted_obj['一本理科'].max() - sorted_obj['一本理科'].min()
print("6) 一本理科分数线 最大值与最小值之差：", diff_1li)
print("\n")

# 任务 7) 二本文科最大值与最小值之差
diff_2wen = sorted_obj['二本文科'].max() - sorted_obj['二本文科'].min()
print("7) 二本文科分数线 最大值与最小值之差：", diff_2wen)
print("\n")

# 任务 8) 二本理科最大值与最小值之差
diff_2li = sorted_obj['二本理科'].max() - sorted_obj['二本理科'].min()
print("8) 二本理科分数线 最大值与最小值之差：", diff_2li)
print("\n")

# 任务 9) 按一本文科排序（其实和 sorted_obj 一样，可省略，这里保留你原逻辑）
ser_obj1 = sorted_obj.sort_values(by='一本文科', ascending=False)
print("9) 按一本文科分数线排序后的 DataFrame（前几行）：")
print(ser_obj1.head())
print("\n")

# 任务 10) 2022 与 2021 年 一本文科之差
year_2022 = df_obj[df_obj['年份'] == 2022]['一本文科'].values[0]
year_2021 = df_obj[df_obj['年份'] == 2021]['一本文科'].values[0]
diff_2022_2021_1wen = year_2022 - year_2021
print("10) 2022与2021年一本文科分数线之差：", diff_2022_2021_1wen)
print("\n")

# 任务 11) 按一本理科排序
ser_obj2 = sorted_obj.sort_values(by='一本理科', ascending=False)
print("11) 按一本理科分数线排序后的 DataFrame（前几行）：")
print(ser_obj2.head())
print("\n")

# 任务 12) 2022 与 2021 年 一本理科之差
year_2022_1li = df_obj[df_obj['年份'] == 2022]['一本理科'].values[0]
year_2021_1li = df_obj[df_obj['年份'] == 2021]['一本理科'].values[0]
diff_2022_2021_1li = year_2022_1li - year_2021_1li
print("12) 2022与2021年一本理科分数线之差：", diff_2022_2021_1li)
print("\n")

# 任务 13) 按二本文科排序
ser_obj3 = sorted_obj.sort_values(by='二本文科', ascending=False)
print("13) 按二本文科分数线排序后的 DataFrame（前几行）：")
print(ser_obj3.head())
print("\n")

# 任务 14) 2022 与 2021 年 二本文科之差
ser_obj3 = sorted_obj.sort_values(by='二本文科', ascending=False)
print("13) 按二本文科分数线排序后的 DataFrame（前几行）：")
print(ser_obj3.head())
print("\n")

# 任务 15) 按二本理科排序
ser_obj4 = sorted_obj.sort_values(by='二本理科', ascending=False)
print("15) 按二本理科分数线排序后的 DataFrame（前几行）：")
print(ser_obj4.head())
print("\n")

# 任务 16) 2022 与 2021 年 二本理科之差
year_2022_2li = df_obj[df_obj['年份'] == 2022]['二本理科'].values[0]
year_2021_2li = df_obj[df_obj['年份'] == 2021]['二本理科'].values[0]
diff_2022_2021_2li = year_2022_2li - year_2021_2li
print("16) 2022与2021年二本理科分数线之差：", diff_2022_2021_2li)
print("\n");
# 任务 17) 求 2008 与 2022 年的平均录取分数
year_2008 = df_obj[df_obj['年份'] == 2008]
year_2022_all = df_obj[df_obj['年份'] == 2022]

avg_2008 = year_2008[['一本文科', '一本理科', '二本文科', '二本理科']].mean(axis=1).values[0]
avg_2022 = year_2022_all[['一本文科', '一本理科', '二本文科', '二本理科']].mean(axis=1).values[0]
avg_2008_2022 = (avg_2008 + avg_2022) / 2
print("17) 2008 与 2022 年的平均录取分数：", avg_2008_2022)
print("\n")

# 备选：所有年份所有分数线的总体平均
all_avg = df_obj[['一本文科', '一本理科', '二本文科', '二本理科']].mean().mean()
print("17) 【备选】2008~2022年所有分数线的总体平均录取分数：", all_avg)